﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    class ProductDB
    {
        static string connectionString = "data source=.;Initial Catalog=ShopDB;Integrated Security=True";

        public void Add(Product p)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("INSERT INTO ProductsDB (barCode, name, price, quantity, unitOfMeasure,department, expirationDate) VALUES('{0}', '{1}','{2}', '{3}','{4}', '{5}', '{6}')", p.barCode, p.name, p.price, p.quantity, p.unitOfMeasure, p.department, p.expirationDate);
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void Update(Product p)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("UPDATE ProductsDB SET name = '{1}', price ='{2}', quantity='{3}', unitOfMeasure ='{4}',department ='{5}', expirationDate = '{6}',WHERE barCode ='{0}', p.barCode, p.name, p.price, p.quantity, p.unitOfMeasurement, p.department, p.expirationDate");
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void Delete(Product p)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = string.Format("DELETE FROM ProductsDB WHERE barCode ='{0}', p.barCode");
                SqlCommand command = new SqlCommand(query, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public List<Product> GetProducts()
        {
            List<Product> result = new List<Product>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string quesry = "SELECT * from ProductsDB";
                SqlCommand command = new SqlCommand(quesry, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Product p = new Product();
                    p.barCode = (int)reader["barCode"];
                    p.name = (string)reader["name"];
                    p.price = (int)reader["price"];
                    p.quantity = (int)(reader["quantity"]);
                    p.unitOfMeasure = (string)reader["unitOfMeasure"];
                    p.department = (int)reader["department"];
                    p.expirationDate = (DateTime)reader["expirationDate"];
                    result.Add(p);
                }
                reader.Close();
            }

            return result;
        }
    }
}